/**
 * User数据模型模块
 */

// 导入SQLite3模型
const sqlite3 = require("sqlite3").verbose();
// 导入工具模块
const util = require("../../common/util");
// 导入配置模块
const config = require("../../common/config");

class UserDB{ 
    /**
     * @constructor
     * @private
     */
    constructor() {
        this.dbFile = config.dbFile;
        this.instance = null;
        this.db = null;
        this.register = this.add;
    }

    /**
     * 创建UserDB对象
     * @returns {UserDB} UserDB实例
     */
    static getInstance() {
        if (!this.instance) {
            this.instance = new UserDB();
        }
        return this.instance;
    }

    /**
     * 连接数据库
     * @returns {Promise}
     */
    connect() {
        return new Promise((resolve, reject) => {
            this.db = new sqlite3.Database(this.dbFile, err => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("connect ok.");
                }
            });
        });
    }

    /**
     * 关闭数据库
     * @returns {Promise}
     */
    close() {
        return new Promise((resolve, reject) => {
            this.db.close(err => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve("close ok.");
                }
            });
        });
    }

    /**
     * 获取指定ID的用户信息
     * @param {Number} userId 用户ID
     * @returns {Promise}
     */
    find (userId) {
        return new Promise((resolve, reject) => {
            let sql = "SELECT * FROM users WHERE id = ?";
            let params = [userId];
            this.db.get(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    };

    /**
     * 获取用户信息列表
     * @param {Number} limit 数量
     * @param {Number} offset 开始
     * @returns {Promise}
     */
    findAll (limit = -1, offset = -1) {
        return new Promise((resolve, reject) => {
            let sql = "";
            let params = [];
            if (limit === -1) {
                sql="SELECT * FROM users ORDER BY id"
            } else if (offset === -1) {
                sql = "SELECT * FROM users ORDER BY id LIMIT ?";
                params[0] = limit;
            } else {
                sql = "SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?";
                params[0] = limit;
                params[1] = offset;
            }
            this.db.all(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    };

    /**
     * 新增用户
     * @param {Object} user 用户数据 
     * @returns {Promise}
     */
    add (user) {
        return new Promise((resolve, reject) => {
            let sql = `INSERT INTO users (
                username, password, nickname, truename, avatar,
                role, last_login_time, last_login_ip, created_time,created_ip,
                updated_time
            ) VALUES (
                ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?,
                ?
            );`;
            let params = [
                user.username,
                user.password,
                user.nickname,
                user.truename,
                user.avatar,
                user.role,
                user.lastLoginTime,
                user.lastLoginIP,
                user.createdTime,
                user.createdIP,
                user.updatedTime
            ];
            
            this.db.run(sql, params, function(err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.lastID);// 插入的数据的自增ID
                }
            });
        });
    }

    /**
     * 修改用户
     * @param {Object} user 用户数据 
     * @returns {Promise}
     */
    update (user) {
        return new Promise((resolve, reject) => {
            let sql = `UPDATE users SET
                username = ?, password = ?, nickname = ?, truename = ?,
                avatar = ?, role = ?, updated_time = ?
                WHERE id = ?
            ;`;
            let params = [
                user.username,
                user.password,
                user.nickname,
                user.truename,
                user.avatar,
                user.role,
                user.updatedTime,
                user.id
            ];
            
            this.db.run(sql, params, function(err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.changes);// 影响的记录数
                }
            });
        });
    };

    /**
     * 删除用户
     * @param {Number} userId 用户ID
     * @returns {Promise}
     */
    remove (userId) {
        return new Promise((resolve, reject) => {
            let sql = "DELETE FROM users WHERE id = ?";
            let params = [userId];
            this.db.run(sql, params, function(err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.changes);// 影响的记录数
                }
            });
        });
    }

    /**
   * 获取用户数量
    * @returns {Promise}
     */
    getCount(){
        return new Promise((resolve,reject)=>{
            let sql = "SELECT count(1) AS total FROM users";
            let params = [];
            this.db.get(sql,params,function(err,result){
                if(err){
                    util.err(err);
                    reject(err);
                }
                else{
                    resolve(result);
                }
            });
        });
    }

    /**
     * 搜索用户
     * @param {String} q 查询关键字
     * @param {Number} limit 数量
     * @param {Number} offset 偏移
     * @returns {Promise}
     */
    search (q, limit = -1, offset = -1) {
        return new Promise((resolve, reject) => {
            let sql = "";
            q = `%${q}%`;
            let params = [q, q, q];
            if (limit === -1) {
                sql="SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ?"
            } else if (offset === -1) {
                sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ?";
                params.push(limit);
            } else {
                sql = "SELECT * FROM users WHERE username LIKE ? OR nickname LIKE ? OR truename = ? LIMIT ? OFFSET ?";
                params.push(limit);
                params.push(offset);
            }
            this.db.all(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    }

    /**
     * 用户登录
     * @param {String} username 用户名
     * @param {String} password 密码
     * @returns {Promise}
     */
    login(username, password) {
        return new Promise((resolve, reject) => {
            let sql = "SELECT * FROM users WHERE username = ? AND password = ?";
            let params = [username, password];
            this.db.get(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(result);
                }
            });
        });
    }

    /**
     * 更新用户登录相关数据
     * @param {String} username 用户名
     * @param {Object} lastLogin 登录状态对象
     * @returns {Promise}
     */
    touch(username, lastLogin) {
        return new Promise((resolve, reject) => {
            let sql = `UPDATE users SET 
                last_login_time = ?, last_login_ip = ?, login_count = login_count + 1
                WHERE username = ?`;
            let params = [lastLogin.time, lastLogin.ip, username];
            this.db.run(sql, params, (err, result) => {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.changes);
                }
            });
        });
    }

    /**
     * 修改用户个人信息
     * @param {Number} userId 用户ID
     * @param {Object} info 用户信息 
     * @returns {Promise}
     */
    changeInfo(id, info) {
        return new Promise((resolve, reject) => {
            let sql = `UPDATE users SET nickname = ?, truename = ?, avatar = ? WHERE id = ?;`;
            let params = [
                info.nickname,
                info.truename,
                info.avatar,
                id
            ];
            
            this.db.run(sql, params, function(err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.changes);// 影响的记录数
                }
            });
        });
    }

    /**
     * 修改用户密码
     * @param {Number} userId 用户ID
     * @param {String} password 用户密码
     * @returns {Promise}
     */
    changePassword(id, password) {
        return new Promise((resolve, reject) => {
            let sql = `UPDATE users SET password = ? WHERE id = ?;`;
            let params = [password, id];
            
            this.db.run(sql, params, function(err, result) {
                if (err) {
                    util.err(err);
                    reject(err);
                } else {
                    resolve(this.changes);// 影响的记录数
                }
            });
        });
    }
}

module.exports = UserDB;